
package com.feib.soeasy.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.hibernate.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.feib.soeasy.dao.Soez5008Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.pagination.PaginatedList;


/**
 * @title (#)Soez5008JdbcDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Repository("soez5008Dao")
public class Soez5008JdbcDao extends JdbcDaoSupport implements Soez5008Dao {
	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	@Resource(name = "dataSource")
	public void setMyDataSource(DataSource dataSource) {
		super.setDataSource(dataSource); 
	}

	//Julian
	@Override
	public List queryCommissionCode(String channel ,Date qryDateFrom, Date qryDateTo )
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct substring(barcdoe1,len(barcdoe1)-2,3) as code from feedback_log ");
		sql.append("where feedback_type = 2 ");
		if ((channel != null) && (!channel.equals("H"))) //非郵局
			sql.append("and barcdoe1 is not null ");
		sql.append("and channel = ? ");
		sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");
		sql.append("order by code ");

		List<Object> args = new ArrayList<Object>();

		if(channel != null){
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(qryDateFrom));
		args.add(dateFormat.format(qryDateTo));

		logger.debug("5008queryFeedBackLog sql={};  args={}", sql, args);

		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
	}


	//Julian
	@Override
	public List queryFeedbackLog(String channel ,String code, Date qryDateFrom, Date qryDateTo )
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select substring(barcdoe1,len(barcdoe1)-2,3) as code, substring(fbfilename,len(fbfilename)-7,8) as fbdate, ");
		sql.append("CONVERT(datetime,(CONVERT(varchar(4),(substring(barcdoe1,1,2)+2011))+substring(barcdoe1,3,2)+substring(barcdoe1,5,2))) as dueday ,aday, txamt, ");
		sql.append("case substring(userno,len(userno)-1,2) when '00' then substring(userno,1,14) else userno end as userno ");
		sql.append("from feedback_log ");
		sql.append("where feedback_type = 2 ");
		sql.append("and channel = ? ");
		if ( code!=null && !code.equalsIgnoreCase("N/A") ) //非郵局
			sql.append("and substring(barcdoe1,len(barcdoe1)-2,3) = ? ");
		sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");
		sql.append("order by code, fbdate ");

		List<Object> args = new ArrayList<Object>();

		if(channel != null){
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		if ( code!=null && !code.equalsIgnoreCase("N/A") ) //非郵局
			args.add(code);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(qryDateFrom));
		args.add(dateFormat.format(qryDateTo));

		logger.debug("5008 queryFeedbackLog sql={};  args={}", sql, args);

		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
	}

	/**
	 * 根據通路 代碼 跟日期 取得  顯示畫面的feedback (可分群組)
	 * @author Jeff
	 */
	@Override
	public List<Map<String, Object>> queryGroupFeedbackLog(Group group ,String channel ,String code, Date qryDateFrom, Date qryDateTo )
	{	
		List<Object> args = new ArrayList<Object>();

		String sql =  getQueryGroupFeedbackLogSql(group , channel , code,  qryDateFrom,  qryDateTo,args);

		logger.debug("5008 queryGroupFeedbackLog sql={};  args={}", sql, args);
		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());


	}

	/**
	 * 根據通路跟日期 取得  CommissionCode (可分群組)
	 * @author Jeff
	 */
	@Override
	public List<String> queryGroupCommissionCode(Group group,
			String channel, Date qryDateFrom, Date qryDateTo) {
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();
		sql.append("select distinct substring(barcdoe1,len(barcdoe1)-2,3) as code from feedback_log f ");

		/* 沒有傳入group就抓全部 */
		if(null != group.getGroupPk()){
			sql.append("left join real_account r "); 
			sql.append(" on r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ");			
		}

		sql.append(" where feedback_type = 2 ");
		if ((channel != null) && (!channel.equals("H"))) //非郵局
			sql.append("and barcdoe1 is not null ");
		sql.append("and channel = ? ");

		/* 沒有傳入group就抓全部 */
		if(null != group.getGroupPk()){
			sql.append(" and r.group_pk = ? ");
			args.add(group.getGroupPk());
		}
		sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");
		sql.append("order by code ");

		if(channel != null){
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(qryDateFrom));
		args.add(dateFormat.format(qryDateTo));

		logger.debug("5008queryFeedBackLog sql={};  args={}", sql, args);

		List<String> commissionCode = new ArrayList<String>();

		List<Map<String, Object>> rows = this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
		for (Map<String, Object> row : rows) {

			commissionCode.add((String)row.get("code"));

		}	
		return  commissionCode;
	}


	public String getQueryGroupFeedbackLogSql(Group group ,String channel ,String code, Date qryDateFrom, Date qryDateTo ,List<Object> args)
	{
		StringBuffer sql = new StringBuffer();
		args.clear();
		sql.append("select channel,substring(barcdoe1,len(barcdoe1)-2,3) as code, substring(fbfilename,len(fbfilename)-7,8) as fbdate, ");
		sql.append("CONVERT(datetime,(CONVERT(varchar(4),(substring(barcdoe1,1,2)+2011))+substring(barcdoe1,3,2)+substring(barcdoe1,5,2))) as dueday ,aday, txamt, ");
		sql.append("case substring(userno,len(userno)-1,2) when '00' then substring(userno,1,14) else userno end as userno ");
		sql.append("from feedback_log f ");
		sql.append("left join real_account r "); 		
		sql.append("on r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ");


		sql.append(" where feedback_type = 2 ");
		sql.append("and channel = ? ");
		/* 沒有傳入group就抓全部 */
		if(null != group.getGroupPk()){	
			sql.append(" and r.group_pk = ? ");
			args.add(group.getGroupPk());
		}
		if ( code!=null && !code.equalsIgnoreCase("N/A") ) //非郵局
			sql.append("and substring(barcdoe1,len(barcdoe1)-2,3) = ? ");
		sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");
		sql.append("order by code, fbdate ");



		if(channel != null){
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		if ( code!=null && !code.equalsIgnoreCase("N/A") ) //非郵局
			args.add(code);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(qryDateFrom));
		args.add(dateFormat.format(qryDateTo));

		logger.debug("getQueryGroupFeedbackLogSql SQL="+sql+"; args="+args);
		return sql.toString();
	}

	/**
	 * TODO
	 * @param group
	 * @param channel
	 * @param code
	 * @param qryDateFrom
	 * @param qryDateTo
	 * @param args
	 * @return
	 */
	public String getQueryTotalGroupFeedbackLogSql(Group group ,String[] channel ,String code, Date qryDateFrom, Date qryDateTo ,List<Object> args)
	{
		StringBuffer sql = new StringBuffer();
		args.clear();
		sql.append("select substring(barcdoe1,len(barcdoe1)-2,3) as code, substring(fbfilename,len(fbfilename)-7,8) as fbdate, ");
		sql.append("CONVERT(datetime,(CONVERT(varchar(4),(substring(barcdoe1,1,2)+2011))+substring(barcdoe1,3,2)+substring(barcdoe1,5,2))) as dueday ,aday, txamt, ");
		sql.append("case substring(userno,len(userno)-1,2) when '00' then substring(userno,1,14) else userno end as userno ");
		sql.append("from feedback_log f ");
		/* 沒有傳入group就抓全部 */
		if(null != group.getGroupPk()){
			sql.append(" left join real_account r "); 
			sql.append(" on r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ");
		}

		sql.append(" where feedback_type = 2 ");

		sql.append("and channel = ? ");
		/* 沒有傳入group就抓全部 */
		if(null != group.getGroupPk()){

			sql.append(" and r.group_pk = ? ");
			args.add(group.getGroupPk());
		}

		if ( code!=null && !code.equalsIgnoreCase("N/A") ) //非郵局
			sql.append("and substring(barcdoe1,len(barcdoe1)-2,3) = ? ");
		sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");
		sql.append("order by code, fbdate ");



		if(channel != null){
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		if ( code!=null && !code.equalsIgnoreCase("N/A") ) //非郵局
			args.add(code);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(qryDateFrom));
		args.add(dateFormat.format(qryDateTo));

		logger.debug("getQueryGroupFeedbackLogSql SQL="+sql+"; args="+args);
		return sql.toString();
	}



	/**TODO jeff 取得明細(根據群組)*/
	public PaginatedList<Map<String, Object>> queryGroupFeedbackLog(Group group ,String channel ,String code, Date qryDateFrom, Date qryDateTo , PaginatedList<Map<String, Object>> paginationConfig)
	{
		List<Object> args = new ArrayList<Object>();
		String sql = getQueryGroupFeedbackLogSql(group , channel , code,  qryDateFrom,  qryDateTo,args);
		logger.debug("queryByGroupNo SQL="+sql+"; args="+args);

		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();
		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);

		return extracotr.getPaginatedList();

	}

	/**TODO jeff
	 * 根據畫面傳來的通路 作分類
	 * 
	 * 
	 * */
	public PaginatedList<Map<String, Object>> queryTotalGroupFeedbackLog(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , PaginatedList<Map<String, Object>> paginationConfig)
	{

		Map<String, String> channelCode=new HashMap<String, String>();

		channelCode.put("D", "7111111");
		channelCode.put("E", "TFM");
		channelCode.put("F", "HILIFE");
		channelCode.put("G", "OKCVS");
		channelCode.put("H", "POST");

		List<Object> args = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		int count=0;
		for(int i=0;i<channels.length;i++){
			String channel = channels[i];
			if(null!=channel){
				count++;
			}
		}


		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(null!=channel){
				sql.append("select  channel,substring(barcdoe1,len(barcdoe1)-2,3) as code, substring(fbfilename,len(fbfilename)-7,8) as fbdate, ");
				sql.append("CONVERT(datetime,(CONVERT(varchar(4),(substring(barcdoe1,1,2)+2011))+substring(barcdoe1,3,2)+substring(barcdoe1,5,2))) as dueday ,aday, txamt, ");
				sql.append("case substring(userno,len(userno)-1,2) when '00' then substring(userno,1,14) else userno end as userno ");
				sql.append(",a.GROUP_PK as grouppk ,a.GROUP_NO as groupno ,a.GROUP_NAME as gname ,POST_LEVEL1 as  post1,POST_LEVEL2  as post2,POST_LEVEL3  as post3 ");
				sql.append(" from feedback_log f ");
				sql.append("left join  real_account r ");
				sql.append("on r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ");								
				sql.append("left join app_group  a ");			
				sql.append(" on r.group_pk=a.group_pk ");			
				sql.append(" where feedback_type = 2 ");
				sql.append("and channel = ? ");					
				args.add(channelCode.get(channel));
				/* 沒有傳入group就抓全部 */
				if(null != group.getGroupPk()){
					sql.append(" and r.group_pk = ? ");
					args.add(group.getGroupPk());
				}


				sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");

				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
				args.add(dateFormat.format(dateFrom));
				args.add(dateFormat.format(dateTo));

				if(i<count-1){
					sql.append(" union ");

				}
			}
		}

		sql.append("order by  channel,code, fbdate ");


		logger.debug("queryByGroupNo SQL="+sql+"; args="+args);

		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();
		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);
		/*釋放資源 */
		channelCode.clear();
		channelCode=null;
		return extracotr.getPaginatedList();

	}


	class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>>
	{
		private RowMapper<T> rowMapper;
		private PaginatedList<T> paginatedList;

		public RowMapperResultSetExtractor(RowMapper<T> rowMapper, PaginatedList<T> paginatedList) {
			Assert.notNull(rowMapper, "RowMapper is required");
			Assert.notNull(paginatedList, "PaginatedList is required");
			this.rowMapper = rowMapper;
			this.paginatedList = paginatedList;
		}


		public List<T> extractData(ResultSet rs) throws SQLException {
			List<T> results = new ArrayList<T>();

			int maxResults = paginatedList.getPageSize(); //the maximum number of results
			int firstResult = paginatedList.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			int lastResult = firstResult + maxResults -1;  //the first result to retrieve, numbered from 0

			int rowNum = 0;
			while (rs.next()) {

				if (firstResult <= rowNum && lastResult >= rowNum)
					results.add(this.rowMapper.mapRow(rs, rowNum++));
				else
					rowNum++;
			}

			paginatedList.setFullListSize(rowNum);
			paginatedList.setPaginatedList(results);
			return results;

		}



		/**
		 * @return the paginatedList
		 */
		public PaginatedList<T> getPaginatedList() {
			return paginatedList;
		}
	}



	/*    //julian
    @Override
    public List sumChainstoreAmount(String channel ,
    		Date qryDateFrom, Date qryDateTo , String commissionCode, List commissionCodes )
    {
    	StringBuffer sql = new StringBuffer();
    	sql.append("select count(*) as count, sum(txamt) as txamt ");
    	sql.append("from feedback_log f ");
    	sql.append("where f.feedback_type = 2 ");
    	if ((channel != null) && (!channel.equals("H"))) //非郵局
    		sql.append("and substring(barcdoe1,len(barcdoe1)-2,3) = ? ");
    	else
    		sql.append("and substring(barcdoe1,len(barcdoe1)-2,3) is ? ");
    	sql.append("and f.channel = ? ");
    	sql.append("and substring(fbfilename,len(fbfilename)-7,8) between ?  and ? ");
    	sql.append("group by substring(barcdoe1,len(barcdoe1)-2,3) ");

    	List<Object> args = new ArrayList<Object>();

    	if (commissionCode.equalsIgnoreCase("null"))
    		commissionCode = null;
    	args.add(commissionCode);

    	if(channel != null){
	    	if(channel.equals("D"))
	    		args.add("7111111");
	    	else if(channel.equals("E"))
	    		args.add("TFM");
	    	else if(channel.equals("F"))
	    		args.add("HILIFE");
	    	else if(channel.equals("G"))
	    		args.add("OKCVS");
	    	else if(channel.equals("H"))
	    		args.add("POST");
    	}

    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
    	args.add(dateFormat.format(qryDateFrom));
    	args.add(dateFormat.format(qryDateTo));

    	logger.debug("5008 sumChainstoreAmount sql={};  args={}", sql, args);

    	return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
    }
	 */    


	/**
	 * 超商統計資料
	 * @param groupPk 帳單業者
	 * @param channel 通路, D:7-11, E:OK, F:全家, G:萊爾富, NULL: 4家超商
	 * @param qryDateType 資料期間基礎：1: 實際入帳日  2:繳款交易日。
	 * @param qryDateFrom 資料期間起
	 * @param qryDateTo 資料期間迄
	 * @return 
	 */
	/*    public List<Map<String, Object>> getStatisticsForStore(Long groupPk, String channel, Integer qryDateType, Date qryDateFrom, Date qryDateTo)
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();

        sql.append("select W.WRITE_OFF_PAYMENT_PK , P.PAYMENT_PK, P.PAYMENT_PK, U.FORMAT_DEF_PK");
        //sql.append(", P.BARCODE_STORES_1, P.BARCODE_STORES_1_LOAN");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) AS COMMISSION_CODE_LOAN");
        sql.append(", CASE W.AMOUNT WHEN P.TOTAL_LOANABLE_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) ELSE SUBSTRING( P.BARCODE_STORES_1, 7, 9) END AS COMMISSION_CODE");
        sql.append(", P.TOTAL_AMOUNT, P.TOTAL_LOANABLE_AMOUNT, W.AMOUNT");
        sql.append(", W.WRITE_OFF_DATE, W.CREATED_DATE");
        sql.append(" FORM WRITE_OFF_PAYMENT W INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK");

        sql.append(" WHERE P.PAYMENT_STATUS = 5"); //已繳款
        sql.append(" AND P.GROUP_PK = ?"); // 帳單業者
        args.add(groupPk);
        sql.append(" AND CHANNEL = ?"); // 通路
        args.add(channel);
        // 資料期間
        if (new Integer(1).equals(qryType))
            sql.append(" AND W.CREATED_DATE between ? and ?"); // 實際入帳日
        else
            sql.append(" AND W.WRITE_OFF_DATE between ? and ?"); // 繳款交易日

        args.add(qryDateFrom);
        args.add(qryDateTo);


         第二個版本 
        sql.append("SELECT WRITE_OFF_DATE, ");
        sql.append("CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME, COMMISSION_CODE ");

          電金部2011/05/09報表邏輯異動 
        1.手續費負擔別為"內含"時,"業者負擔"="超商成本"
        2.手續費負擔別為"外加"時,  "業者負擔"+  "繳費者負擔"  ="超商成本"
                    銀行端實際撥款金額(內含) =>  代收金額  -  "業者負擔"
                    銀行端實際撥款金額(外加) =>  代收金額   -  "業者負擔"
                    客戶端端實際撥款金額(內含) =>   代收金額-   代收手續費 ( "業者負擔"  )   -帳務處理費
                    客戶端實際撥款金額(外加) =>     代收金額-    代收手續費 ( "業者負擔"  ) -帳務處理費

        //2011/05/13 jeff Gu  start   修正    代收手續費 ( "業者負擔"  )
        sql.append(", (GROUP_AMOUNT) AS COMMISSION_AMOUNT");
        //2011/05/13 jeff Gu  end 


        // 代收筆數
        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ");
        //代收金額       
        sql.append(", SUM(AMOUNT) AS AMOUNT ");
        //2011/05/13 jeff Gu  start   修正     銀行端實際撥款金額 =>  代收金額  -  "業者負擔"
        sql.append(", SUM(AMOUNT -(GROUP_AMOUNT))  AS REAL_AMOUNT ");
        //2011/05/13 jeff Gu  end 
        sql.append("FROM ");
        sql.append("( ");
        sql.append("SELECT TMP.* ");
        sql.append(", C.COMMISSION_CODE_PK ");
        sql.append(", C.COMMISSION_CODE_NAME ");
        sql.append(", C.COMMISSION_TYPE ");
        sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ");
        sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ");
        sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ");
        sql.append(", ISNULL(GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ");
        sql.append("FROM ");
        sql.append("( ");
        sql.append("select W.WRITE_OFF_PAYMENT_PK ");
        sql.append(", P.PAYMENT_PK ");
        sql.append(", U.FORMAT_DEF_PK ");
        sql.append(", W. AMOUNT ");
        sql.append(", convert(char(8), W.WRITE_OFF_DATE,112 ) as WRITE_OFF_DATE");
        sql.append(", convert(char(8), W.TX_DATE,112 ) as TX_DATE");
        sql.append(", W.CREATED_DATE ");
        sql.append(", P.BARCODE_STORES_1 ");
        //sql.append(", P.BARCODE_STORES_1_LOAN ");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE ");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) AS COMMISSION_CODE_LOAN ");
        sql.append(", P.TOTAL_AMOUNT ");
        sql.append(", P.TOTAL_LOANABLE_AMOUNT ");
        sql.append(", W.CHANNEL ");
        sql.append(", CT.CODE_NAME AS CHANNEL_NAME");
        sql.append(", CASE  W.AMOUNT WHEN P.TOTAL_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1, 7, 9) ELSE  SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) END AS COMMISSION_CODE ");
        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ");
        sql.append(" INNER JOIN CODE_TABLE CT ON CT.CODE_KIND='CHANEL' AND CT.CODE_VALUE = W.CHANNEL ");
        sql.append(" where P.PAYMENT_STATUS = 5 "); //已繳款

        if(null != groupPk ){
        sql.append(" AND P.GROUP_PK = ? "); // 帳單業者
        args.add(groupPk);
        }
        if (null != channel)
        {
            sql.append(" AND W.CHANNEL = ? "); // 通路
            args.add(channel);
        }
        else
        {
            sql.append(" AND W.CHANNEL in( ?, ?, ?, ?) "); // 通路
            args.add(WriteOffPayment.CHANNEL_7_11);
            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
            args.add(WriteOffPayment.CHANNEL_HILIFE);
            args.add(WriteOffPayment.CHANNEL_OK_MART);
        }

        // 資料期間 1: 銷帳日期  2:實際繳款日
        if (new Integer(1).equals(qryDateType))
            sql.append(" AND W.WRITE_OFF_DATE between ? and ? "); // 銷帳日期
        else
            sql.append(" AND W.TX_DATE between ? and ? "); // 實際繳款日

        args.add(qryDateFrom);
        args.add(qryDateTo);

        sql.append(") TMP ");
        sql.append(" ");
        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ");
        sql.append("INNER  JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ");
        sql.append(") D ");
        //2011/05/13 jeff Gu  start   修正     銀行端實際撥款金額 =>  代收金額  -  "業者負擔"
        sql.append("GROUP BY WRITE_OFF_DATE, CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME, COMMISSION_CODE, COMMISSION_AMOUNT, PAYER_AMOUNT ,GROUP_AMOUNT ");
        //2011/05/13 jeff Gu  end 
        sql.append("ORDER BY WRITE_OFF_DATE, CHANNEL, COMMISSION_CODE_PK ");

        logger.debug("5008getStatisticsForStore sql={};  args={}", sql, args);

        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());

    }
	 */    
	/*    @Override
    public List queryFeedBackLog(Group group , Date adayS, Date adayE, String channel, String commissionCode)
    {
    	StringBuffer sql = new StringBuffer();
    	sql.append("select f.*,convert(varchar(8),f.fbtime,112) as fbtime from feedback_log f , real_account r ");
    	sql.append("where barcdoe1 is not null ");
    	sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ");
    	sql.append("and r.group_pk = ? ");
    	sql.append("and f.err_msg is not null ");
    	sql.append("and f.feedback_type = 2 ");

    	if(channel != null)
    		sql.append("and f.channel = ? ");

    	sql.append("and convert(varchar(8),f.fbtime,112) between ?  and ? ");

    	if(commissionCode != null)
    		sql.append("and  substring(F.barcdoe1, 7,3) = ? ");

    	List<Object> args = new ArrayList<Object>();
    	args.add(group.getGroupPk());

    	if(channel != null){
	    	if(channel.equals("D"))
	    		args.add("7111111");
	    	else if(channel.equals("E"))
	    		args.add("TFM");
	    	else if(channel.equals("F"))
	    		args.add("HILIFE");
	    	else if(channel.equals("G"))
	    		args.add("OKCVS");
	    	else if(channel.equals("H"))
	    		args.add("POST");
    	}

    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
    	args.add(dateFormat.format(adayS));
    	args.add(dateFormat.format(adayE));

    	if(commissionCode != null)
    		args.add(commissionCode);

    	logger.debug("5008queryFeedBackLog sql={};  args={}", sql, args);

    	return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
    }
	 */    

	/*    public List<Map<String, Object>> findCommissionCodesByFormatDefPk(Long formatDefPk)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("select distinct");
        sql.append(" COMMISSION_CODE_TABLE.COMMISSION_CODE_PK");
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE");
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE_NAME");
        sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_S");
        sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_E");
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_TYPE"); // 手續費類別 I:內含 , O:外加
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_AMOUNT");
        sql.append(", COMMISSION_CODE_TABLE.BANK_AMOUNT"); //本行帳務處理費
        sql.append(", COMMISSION_CODE_TABLE.PAYER_AMOUNT"); // 繳款者負擔(外加時有用)
        sql.append(", FORMAT_DEF_COMMISSION.FORMAT_DEF_PK");
        sql.append(" from COMMISSION_CODE_TABLE");
        sql.append(" inner join FORMAT_DEF_COMMISSION ");
        sql.append(" on COMMISSION_CODE_TABLE.COMMISSION_CODE_PK = FORMAT_DEF_COMMISSION.COMMISSION_CODE_PK");
        sql.append(" where FORMAT_DEF_COMMISSION.FORMAT_DEF_PK = ?");
        return this.getJdbcTemplate().queryForList(sql.toString(), new Object[]{formatDefPk});
    }
	 */}
